from datetime import date, datetime
from itertools import count
import select
from typing import List
from unittest import result

from fastapi_pagination.ext.databases import paginate
from sqlalchemy import delete, desc, insert, select, update, func, and_
from app.api.admin.model.coupon.coupon import CouponCateModel, CouponModel
from app.api.admin.model.order.order import OrderModel
from app.api.admin.schema.coupon.coupon import CouponDeleteInSchema, CouponEditInSchema, CouponInSchema, CouponListInSchema, CouponOutSchema, CouponStatusInSchema
from app.dependencies.database import db


class CouponService:
    async def add(self, coupon_in: CouponInSchema):
        print(coupon_in.model_dump())
        return await db.execute(insert(CouponModel).values(coupon_in.model_dump()))
        pass

    async def edit(self, edit_in: CouponEditInSchema):
        print("edit_in", edit_in.model_dump())
        return await db.execute(update(CouponModel).where(CouponModel.id == edit_in.id).values(edit_in.model_dump()))
        pass

    async def list(self, coupon_list_in: CouponListInSchema):
        where = []
        if coupon_list_in.coupon_cate_id:
            where.append(CouponModel.cate_id == coupon_list_in.coupon_cate_id)
        if coupon_list_in.valid == "all":
            pass
        if coupon_list_in.valid == "valid":
            # where.append(CouponModel.end_time >= datetime.now())
            where.append(CouponModel.status == 1)
        if coupon_list_in.valid == "invalid":
            where.append(CouponModel.end_time <= datetime.now())
        query = (
            select(
                CouponModel,
                func.count(OrderModel.id).label("use_count"),
                CouponCateModel.name.label("cate_name"),
            )
            .select_from(CouponModel)
            .outerjoin(CouponCateModel, CouponModel.cate_id == CouponCateModel.key)
            .outerjoin(OrderModel, and_(OrderModel.coupon_id == CouponModel.id, OrderModel.status == 2))
            .where(and_(*where))
            .group_by(CouponModel.id, CouponCateModel.name)
            .order_by(desc(CouponModel.id))
        )
        # records = await db.fetch_all(query)
        result = await paginate(db, query)
        result.list = [CouponOutSchema.model_validate(
            item, from_attributes=True).model_dump(by_alias=True) for item in result.list]
        return result

    async def delete(self, del_in: CouponDeleteInSchema):
        return await db.execute(delete(CouponModel).where(CouponModel.id == del_in.id))
        pass

    async def detail(self, coupon_id: int):
        query = (
            select(
                CouponModel,
                CouponCateModel.name.label("cate_name"),
            )
            .select_from(CouponModel)
            .outerjoin(CouponCateModel, CouponModel.cate_id == CouponCateModel.key)
            .where(CouponModel.id == coupon_id)
        )
        result = await db.fetch_one(query)
        return CouponOutSchema.model_validate(result).model_dump(by_alias=True)

    async def change_status(self, status_in: CouponStatusInSchema):
        return await db.execute(update(CouponModel).where(CouponModel.id == status_in.id).values(status=status_in.status))

    @classmethod
    async def instance(cls) -> "CouponService":
        return cls()
